package com.example.service;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.example.dbpractise1.DBOpenHelper;
import com.example.model.Person;

public class PersonService {
	private static final String TAG = "PersonService";
	private static String DB_NAME = "person";
	private static String FIELD_NAME = "name";
	private	static String FIELD_AMOUNT = "amount";
	private static String FIELD_ID = "_id";
	 private DBOpenHelper dbOpenHelper;
	 public static final int NO_VALUE = -1;
	 
	 public PersonService(Context context) {
		 dbOpenHelper = new DBOpenHelper(context);
	 }
	 
	 public void insert(Person person) { 
		 SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		 db.execSQL(String.format("insert into %s (%s, %s) values (?,?)", new String[] {DB_NAME, FIELD_NAME, FIELD_AMOUNT}), new Object[] {person.getName(), person.getAmount()});
	 } 
	 
	 //银行转账，如果金额小于0怎么办
	 public void transactionOperation(int in, int out) {
		 SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		 db.beginTransaction();
		 
		 try {
//			 Cursor cursor = db.rawQuery("select amount from person where _id=?", new String[] {Integer.toString(out)});
//			 if (cursor.moveToFirst()) {
//				 long amount = cursor.getLong(cursor.getColumnIndex("amount"));
//				 
//			 }
			 db.execSQL("update person set amount=amount-10 where _id=?", new Object[]{out});
			 db.execSQL("update person set amount=amount+10 where _id=?", new Object[] {in});
			 db.setTransactionSuccessful();
		 } finally { 
			 db.endTransaction();
		 }
	 }
	 
	 public void update(Person person) {
		 SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		 
		 if (person.getName() != null && !"".equals(person.getName())) {
			 
			 if (person.getPersonId() == NO_VALUE) {
				 db.execSQL("update person set name=?", new Object[] {person.getName()});
			 }else {
				 db.execSQL("update person set name=? where _id=?", new Object[] {person.getName(), person.getPersonId()});
			 }
		 }
	 }
	 
	 public void delete(int id) {
		 SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		 
		 if (id == NO_VALUE) {
			 db.execSQL("delete from person");
		 }else {
			 db.execSQL("delete from PERSON where _id=?", new Object[] {id});
		 }
	 }
	 
	 public List<Person> qurey(int id) {
		 SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		 List<Person> list = new ArrayList<Person>();
		 Cursor cursor = null;
		 
		 if (id == NO_VALUE) {
			 cursor = db.rawQuery("select _id,name,amount from person", null);
		 }else {
			 cursor = db.rawQuery("select _id,name,amount from person where _id=?", new String[] {Integer.toString(id)});
		 }
		 
		 while(cursor.moveToNext()) {
			 int personId = cursor.getInt(cursor.getColumnIndex("_id"));
			 String name = cursor.getString(cursor.getColumnIndex("name"));
			 long amount = cursor.getLong(cursor.getColumnIndex("amount"));
			 list.add(new Person().setPersonId(personId).setName(name).setAmount(amount));
		 }
		 
		 return list;
	 }
	 
	 public List<Person> pageQurey(int offSet, int maxResult) {
		 SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		 List<Person> list = new ArrayList<Person>();
		 
		 if (offSet != NO_VALUE && maxResult != NO_VALUE) {
			 Cursor cursor = db.rawQuery("select * from person limit ?, ?", new String[] {Integer.toString(offSet), Integer.toString(maxResult)});
			 
			 while(cursor.moveToNext()) {
				 int id = cursor.getInt(cursor.getColumnIndex("_id"));
				 String name = cursor.getString(cursor.getColumnIndex("name"));
				 long amount = cursor.getLong(cursor.getColumnIndex("amount"));
				 list.add(new Person().setPersonId(id).setName(name).setAmount(amount));
			 }
		 }
		 
		 return list;
	 }
	 
	 public int count() {
		 SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		 Cursor cursor = db.rawQuery("select count(*) count from person", null);
		 cursor.moveToFirst();
		 int count = cursor.getInt(cursor.getColumnIndex("count"));
		 return count;
	 }

}
